# -*- coding:utf-8 -*-

import os
import time
import logging
import itchat
import shutil
import datetime
from cmd_excel import cmd_excel

# 判断是否存在指定的文件夹，如果不存在则新建
def mkdir(path):
    folder = os.path.exists(path)
    if not folder:
        os.makedirs(path)
    else:
        pass

# 获取前几日的日期列表
def get_date_list(n):
    now_date = datetime.datetime.now()
    date_list = []
    for i in range(0, n):
        new_date = now_date - datetime.timedelta(i)
        date_list.append(new_date.strftime('%m/%d/%Y'))
    return date_list

# 获取前n日的日期
def get_date(n):
    now_date = datetime.datetime.now()
    new_date = now_date - datetime.timedelta(n)
    return new_date.strftime('%#m/%#d/%Y')      # 井号可以去掉单数日期和月份前面的数字0，比如 05月 会返回 5月



# 下面是登录微信，发送图片和文本
# logging.info('登录微信')
# itchat.auto_login(hotReload= True)   # 登录微信网页版，热登录，希望不要让我重新登录

# 获取目标人的username
def get_username(nickname):
    try:
        username = itchat.search_friends(nickname)[0]['UserName']
        return username
    except Exception as e:
        print(e)
        pass

# 发送图片
def send_image(path, username):
    try:
        result = itchat.send_image(path, username)
        if list(result.values())[0]['ErrMsg'] == '请求成功':
            logging.info('发送成功')
        else:
            logging.info('发送失败')
    except:
        print('fail')


# 获取指定路径下的图片文件的绝对路径
def get_file_path(path):
    file_path = []
    lis_file = os.listdir(path)
    for file in lis_file:
        if os.path.splitext(file)[1] == '.png':
            file_path.append(os.path.join(path, file))
    return file_path






send_date = datetime.datetime.now().strftime('%Y%m%d')
before_date = (datetime.datetime.now() - datetime.timedelta(1)).strftime('%Y%m%d')

# sheet roi7需要用到的日期
roi_visible_date = get_date(6)
roi_unvisible_date = get_date(30)

# 中东地区需要隐藏的日期
ar_hide_date = get_date(11)

# 一般工作表需要用到的日期
yesterday = get_date(2)   # 这个日期一会儿需要改一下啊 别忘了
hide_date = get_date(5)

path = 'D:/ribao/file/' + '20190213' + '.xlsx'  # 要操作的Excel路径   # 先手动改一下
mkdir('D:/ribao/pic/' + send_date)  # 创建一下存放图片的路径
path2 = 'D:/ribao/pic/' + send_date  # 要存放图片的路径
print(send_date)
print(before_date)
print(path)
print(path2)


worksheet = ['收入透视','花费透视','安装花费汇总','日活留存汇总','安卓汇总','安卓分渠道','IOS汇总','IOS分渠道','ROI7','中东分层分国家']
pivot_lists = ["数据透视表1","数据透视表2","数据透视表3","数据透视表4"]
screen_sheet =['安装花费汇总','日活留存汇总','安卓汇总','安卓分渠道','IOS汇总','IOS分渠道']
screen_range = {
	'安装花费汇总':'A1:J33',
	'日活留存汇总':'C1:K39',
	'安卓汇总':'A1:I29',
	'安卓分渠道':'A1:T30',
	'IOS汇总':'A1:J29',
	'IOS分渠道':'A1:Z29',
	'中东分层分国家':{
		'中东level1':'A1:AO16',
		'中东level2':'A21:AY36',
		'中东level3':'A41:K56',
		'中东 总':'A66:AE78'
	}
}

print('打开并刷新Excel')
my_excel = cmd_excel(path)  # 实例化，打开要操作的excel,并执行刷新全部数据的操作
print("休息10s")
# time.sleep(30)      # 休息10s
print("刷新数据透视表")
for sheet in worksheet:                         # 刷新每个数据透视表
    print("正在刷新这个sheet: %s" % sheet)
    my_excel.refresh_excel(sheet,"数据透视表1")

for name in pivot_lists:
    my_excel.refresh_excel(worksheet[-1],name)    # 中东的那个表里有四个透视表，都需要刷新一下

print("筛选日期")
# 下面是隐藏和显示日期 这几个工作表
for sheet in ['日活留存汇总','安卓汇总','安卓分渠道','IOS汇总','IOS分渠道']:
    my_excel.filter_date(sheet,pivot_lists[0],new_date = yesterday,old_date= hide_date)

# 隐藏和显示日期，特殊的几个工作表
# 安装花费汇总
my_excel.filter_date("安装花费汇总",pivot_lists[0],new_date= yesterday)

# ROI7
my_excel.filter_date('ROI7',pivot_lists[0], new_date= roi_visible_date, old_date= roi_unvisible_date)

# 中东sheet
for name in pivot_lists:
    my_excel.filter_date('中东分层分国家',name,new_date= yesterday, old_date=ar_hide_date)


# 截图
print("截图保存")
for sheet in screen_sheet:
    print("正在进行的截图：%s" % sheet)
    pic_name = path2 + "/" + sheet + '.png'
    my_excel.excel_catch_screen(sheet, screen_range[sheet],save_name = pic_name)
print("保存并关闭Excel")
my_excel.close_excel()
print("Excel已关闭")

# if __name__ == '__main__':
#     excel_name = r"C:\Users\cm\Desktop\minm\20190212.xlsx"
#     save_name = r"C:\Users\cm\Desktop\minm\123.png"
#     print("打开Excel，并刷新数据。。。")
#     excel = cmd_excel(excel_name)
#     print("10s刷新时间")
#     time.sleep(10)
#     excel.refresh_excel("安装花费汇总","数据透视表1")
#     excel.filter_date("安装花费汇总","数据透视表1","2/13/2019")
#     excel.excel_catch_screen("安装花费汇总","A1:J33",save_name)
#     excel.close_excel()